if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBasicInfoIRB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBasicInfoIRB]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

-- [GetBasicInfoIRB] '2005P001359' --'1999P000002'

CREATE PROCEDURE [dbo].[GetBasicInfoIRB]  
(
	@folderNumber varchar(12)
)

AS
/**************************************************************************
* Name:     GetBasicInfoIRB
*
* Purpose: Provide basic IRB data for Web service
* Notes:	to be called from Web Service IRBInfo
* Output: 2 resultsets
*--------------------------------------------------------------------------
* Date          Initials    Modification
*--------------------------------------------------------------------------
*
* 10/21/2009    	SP          Created 	
* 11/09/2009		SP			Added WITH(NOLOCK)
* 11/13/2009		SP			Bug Resolved for Instituion
***************************************************************************/

SELECT CAST((CASE WHEN PO.[Name] like 'Not Human%' THEN 'Not Human Research'
		WHEN PO.[Name] like 'Hold - Lapsed%' THEN 'Hold - Lapsed'
		WHEN charindex(' ', PO.[Name]) > 0 THEN left(PO.[Name], charindex(' ', PO.[Name]) - 1)
		ELSE PO.[Name]
	END) AS VARCHAR(50)) AS status, 
	ApprovalDate AS approvalDate, 
	ExpirationDate AS expirationDate,
	DU.Username AS userNamePI, 
	AP.FirstName AS firstNamePI, 
	AP.LastName AS lastNamePI, 
	P.Title AS protocolDescription,
	DO.Institution As institution,
	AD.[Name] As sponsorName
	
FROM Protocol P 
		INNER JOIN ProtocolOverallStatusType PO
              ON P.OverallStatusId = PO.Id        
        INNER JOIN AdmPerson AP
              ON AP.Id = P.PIPersonId
        INNER JOIN AdmInternalPerson iP
              ON iP.AdmPersonId = AP.Id
        INNER JOIN dbo.FwkDomainUser DU
              ON AP.FwkDomainUserId = DU.Id
        INNER JOIN AdmSponsor AD
              ON P.AdmSponsorId = AD.Id
        LEFT OUTER JOIN FwkDomainOrganization DO
              ON ip.PrimaryOrganizationID = DO.Id


WHERE P.ProtocolNumber = @folderNumber
	AND P.IsCurrent = '1'
	AND P.DocumentNumber =
		(
		SELECT	max(P1.DocumentNumber)
		FROM	Protocol P1
			INNER JOIN ProtocolOverallStatusType PO
				ON P1.OverallStatusId = PO.Id
		WHERE	P1.ProtocolNumber = P.ProtocolNumber
		)
	

SELECT DISTINCT DU.Username AS userName, AP.FirstName AS firstName, AP.LastName AS lastName ,  
	SUBSTRING(ST.[Name], 1, 45) AS personRole, 
	(CASE WHEN FDM.AccessRightBitmask = 256 THEN 'Yes' ELSE 'No' END) as permission, 
	EA.EmailAddress As emailAddress

FROM dbo.ProtocolStaff_Map PS  WITH(NOLOCK)
	INNER JOIN dbo.AdmPerson AP  WITH(NOLOCK)
		ON AP.Id = PS.AdmPersonId
	INNER JOIN dbo.FwkDomainUser DU  WITH(NOLOCK)
		ON AP.FwkDomainUserId = DU.Id
	INNER JOIN dbo.Protocol P  WITH(NOLOCK)
		ON P.ProtocolNumber = PS.ProtocolNumber
	INNER JOIN dbo.ProtocolOverallStatusType PO  WITH(NOLOCK)
		ON P.OverallStatusId = PO.Id
	INNER JOIN dbo.ProtocolStaffingType ST  WITH(NOLOCK)
		ON PS.ProtocolStaffTypeId = ST.Id 
	INNER JOIN dbo.AdmEmailAddress EA  WITH(NOLOCK)
		ON EA.AdmPersonId = AP.Id
	LEFT OUTER JOIN dbo.FwkResource_DomainUser_Map FDM  WITH(NOLOCK)
		ON FDM.FwkDomainUserId = AP.FwkDomainUserId AND FDM.FwkResourceId = P.FwkResourceId

WHERE P.ProtocolNumber = @folderNumber
	AND P.IsCurrent = '1'
	AND EA.AdmEmailAddressTypeId = 1
	AND P.DocumentNumber =
		(
		SELECT	max(P1.DocumentNumber)
		FROM	Protocol P1
			INNER JOIN ProtocolOverallStatusType PO
				ON P1.OverallStatusId = PO.Id
		WHERE	P1.ProtocolNumber = P.ProtocolNumber
		)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO